package ua.kharkov.knure.dereza.summarytask4.db.dao.mysql;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import ua.kharkov.knure.dereza.summarytask4.db.dao.CardDAO;
import ua.kharkov.knure.dereza.summarytask4.db.dto.CardDTO;

/**
 * Data access object for Cards
 * 
 * @author dereza
 * 
 */
public class MysqlCardDAO implements CardDAO {

	private static final Logger log = Logger.getLogger(MysqlCardDAO.class);

	private Connection conn;

	// ---------------------------------------------
	// SQL queries
	// ---------------------------------------------
	private static final String SQL_FIND_CLIENT_CARDS = "SELECT * FROM cards;";
	private static final String SQL_FIND_CLIENT_CARDS_BY_CLIENT_ID = "SELECT * FROM cards WHERE id IN (SELECT id_card FROM clients_cards WHERE id_client=?);";
	private static final String SQL_FIND_CARD_BY_ID = "SELECT * FROM cards WHERE id=?";
	private static final String SQL_UPDATE_BALANCE = "UPDATE cards SET balance=? WHERE id=?";
	private static final String SQL_UPDATE_STATUS = "UPDATE cards SET status_id=? WHERE id=?";

	@Override
	public List<CardDTO> findClientCards() {
		CardDTO cardDTO = new CardDTO();
		List<CardDTO> list = null;

		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL_FIND_CLIENT_CARDS);
			list = new LinkedList<CardDTO>();
			while (rs.next()) {
				cardDTO = extractCardDTO(rs);
				list.add(cardDTO);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public List<CardDTO> findClientCardsByClientId(int id) {
		CardDTO cardDTO = null;
		List<CardDTO> list = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_CLIENT_CARDS_BY_CLIENT_ID);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			list = new LinkedList<CardDTO>();
			while (rs.next()) {
				cardDTO = extractCardDTO(rs);
				list.add(cardDTO);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return list;
	}

	@Override
	public CardDTO findClientCardById(int id) {
		CardDTO cardDTO = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_CARD_BY_ID);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				cardDTO = extractCardDTO(rs);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return cardDTO;
	}

	@Override
	public void updateBalance(int cardId, BigDecimal balance) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_UPDATE_BALANCE);
			pstmt.setBigDecimal(1, balance);
			pstmt.setInt(2, cardId);
			pstmt.executeUpdate();
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
			throw new Error(ex);
		} finally {
			commitAndClose(null, pstmt, conn);
		}
	}

	@Override
	public void updateStatus(int id, int statusId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_UPDATE_STATUS);
			pstmt.setInt(1, statusId);
			pstmt.setInt(2, id);
			pstmt.executeUpdate();
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
			throw new Error(ex);
		} finally {
			commitAndClose(null, pstmt, conn);
		}
	}

	@Override
	public void updateStatuses(List<Integer> ids, int statusId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			for (int id : ids) {
				pstmt = conn.prepareStatement(SQL_UPDATE_STATUS);
				pstmt.setInt(1, statusId);
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
			throw new Error(ex);
		} finally {
			commitAndClose(null, pstmt, conn);
		}
	}

	@Override
	public CardDTO extractCardDTO(ResultSet rs) throws SQLException {
		CardDTO cardDTO = new CardDTO();
		cardDTO.setId(rs.getInt(1));
		cardDTO.setCode(rs.getString(2));
		cardDTO.setName(rs.getString(3));
		cardDTO.setBalance(rs.getBigDecimal(4));
		cardDTO.setCurrency_id(rs.getInt(5));
		cardDTO.setStatus_id(rs.getInt(6));
		return cardDTO;
	}

	@Override
	public void commitAndClose(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error("Cannot close result set", e);
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				log.error("Cannot close statement", e);
			}
		}
		if (conn != null) {
			try {
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				log.error("Cannot commit and close connection", e);
			}
		}
	}

	@Override
	public void rollback(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				log.error("Cannot rollback", e);
			}
		}
	}
}